﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace University.Workshop.DataAccess
{
    public class CheckInDataAccess : IDisposable
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=DataBaseHotel;Integrated Security=true";
        private SqlConnection cnn;
        public String TokenRoomsForADay(DateTime date)
        {
            if (date == null) { throw new ArgumentException("Invalid Input Parameters"); }

            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT room.Name FROM [DataBaseHotel].[dbo].[room] INNER JOIN [DataBaseHotel].[dbo].[TokenRooms] ON dbo.room.idRoom = dbo.TokenRooms.idRoom ";
            sqlSentence += " WHERE @date BETWEEN [TokenRooms].[CheckIn] AND  [TokenRooms].[CheckOut]";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.Parameters.AddWithValue("@date", date);
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.AddWithValue("@id", idTypeOfRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                String result = null;
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        result += reader["Name"].ToString() + "\n";
                    }
                    return result;
                }
                else { return null; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Searching Booked A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public bool IsToken(int idRoom, DateTime dateTime)
        {
            if (dateTime == null) { throw new ArgumentException("Invalid Input Parameters"); }
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT idRoom FROM  [DataBaseHotel].[dbo].[TokenRooms] ";
            sqlSentence += "WHERE @date BETWEEN [TokenRooms].[CheckIn]  AND  [TokenRooms].[CheckOut] ";
            sqlSentence += " AND idRoom= @id";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.Parameters.AddWithValue("@date", dateTime);
            cmd.Parameters.AddWithValue("@id", idRoom);
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                return reader.HasRows;
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error On Data Base", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public String IsValidRoomCheckIn(string room)
        {
            //if (date == null) { throw new ArgumentException("Invalid Input Parameters"); }

            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"SELECT ClientCI FROM [DataBaseHotel].[dbo].[room] INNER JOIN [DataBaseHotel].[dbo].[TokenRooms] ON dbo.room.idRoom = dbo.TokenRooms.idRoom
                                  WHERE @room = Name ";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.Parameters.AddWithValue("@room", room);
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.AddWithValue("@id", idTypeOfRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                String result = null;
                if (reader.HasRows)
                {
                    /*while (reader.Read())
                    {
                        result += reader["Name"].ToString() + "\n";
                    }*/
                    reader.Read();
                    result = reader["ClientCI"].ToString();
                    return result;
                }
                else { return null; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Searching  Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public void CheckInAClient(int roomId, string clientId, DateTime checkIn, DateTime checkout)
        {
            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"INSERT INTO [DataBaseHotel].[dbo].[TokenRooms]([idRoom],[ClientCI],[CheckIn],[CheckOut])";
            sqlSentence += "VALUES (@IdRoom,@IdClient,@CheckIn,@CheckOut)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@CheckIn", checkIn.ToShortDateString());
            cmd.Parameters.AddWithValue("@CheckOut", checkout.ToShortDateString());
            cmd.Parameters.AddWithValue("@IdRoom", roomId);
            cmd.Parameters.AddWithValue("@IdClient", clientId);

            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Check In  A client", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        #region Miembros de IDisposable

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (cnn != null)
                {
                    cnn.Dispose();
                    cnn = null;
                }
            }
        }

        #endregion

    }
}
